﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.HSSF.Util;
using System.IO;

namespace Core.HandleExcel
{
    /// <summary>
    /// Excel操作类
    /// </summary>
    public class HandleExcel
    {
        #region 导出模版
        /// <summary>
        /// 导出模版
        /// </summary>
        /// <param name="nameStr">表头列名集合</param>
        /// <param name="dir">文件路径</param>
        /// <param name="fileName">文件名</param>
        /// <param name="sheetName">Excel页名</param>
        /// <returns>返回下载字节流</returns>
        public static byte[] Export(List<ExcelEntity> list, string dir, string fileName, string sheetName)
        {
            //新建页面
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();
            ISheet sheet1 = hssfworkbook.CreateSheet(sheetName);

            IRow row = sheet1.CreateRow(0);

            #region 设置表头样式

            ICellStyle headerStyle = hssfworkbook.CreateCellStyle();
            IFont font = hssfworkbook.CreateFont();
            font.FontHeightInPoints = 13;
            font.Boldweight = short.MaxValue;
            headerStyle.SetFont(font);
            headerStyle.Alignment = HorizontalAlignment.CENTER;
            headerStyle.VerticalAlignment = VerticalAlignment.CENTER;
            headerStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THICK;
            headerStyle.BottomBorderColor = HSSFColor.RED.index;

            row.Height = 30 * 20;

            #endregion

            int k = 0;
            foreach (var item in list)
            {
                sheet1.SetColumnWidth(k, item.Width * 256);
                row.CreateCell(k).SetCellValue(item.ColumnName);
                row.GetCell(k).CellStyle = headerStyle;
                k++;
            }

            //判断是否有此文件夹
            if (!Directory.Exists(dir))
            {
                Directory.CreateDirectory(dir);
            }

            //生成文件
            FileStream file = new FileStream(dir + fileName, FileMode.Create);

            //写入文件
            hssfworkbook.Write(file);

            file.Position = 0;

            byte[] by = new byte[file.Length];

            file.Read(by, 0, by.Length);
            file.Close();

            return by;
        }
        #endregion

        #region 导出数据
        /// <summary>
        /// 导出数据
        /// </summary>
        /// <param name="nameStr">表头列名集合</param>
        /// <param name="dir">文件路径</param>
        /// <param name="fileName">文件名</param>
        /// <param name="sheetName">Excel页名</param>
        /// <param name="searchCondition">搜索条件</param>
        /// <returns>返回下载字节流</returns>
        //public static byte[] Export(List<ExcelEntity> list, string dir, string fileName, string sheetName, string searchCondition)
        //{
            //var data = ServiceOrderRepairVWRule.Instance.GetEntityList(searchCondition);

            ////新建页面
            //HSSFWorkbook hssfworkbook = new HSSFWorkbook();
            //ISheet sheet1 = hssfworkbook.CreateSheet(sheetName);

            //IRow row = sheet1.CreateRow(0);

            //#region 设置表头样式

            //ICellStyle headerStyle = hssfworkbook.CreateCellStyle();
            //IFont font = hssfworkbook.CreateFont();
            //font.FontHeightInPoints = 13;
            //font.Boldweight = short.MaxValue;
            //headerStyle.SetFont(font);
            //headerStyle.Alignment = HorizontalAlignment.CENTER;
            //headerStyle.VerticalAlignment = VerticalAlignment.CENTER;
            //headerStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THICK;
            //headerStyle.BottomBorderColor = HSSFColor.RED.index;

            //row.Height = 30 * 20;

            //#endregion

            //int k = 0;
            //foreach (var item in list)
            //{
            //    sheet1.SetColumnWidth(k, item.Width * 256);
            //    row.CreateCell(k).SetCellValue(item.ColumnName);
            //    row.GetCell(k).CellStyle = headerStyle;
            //    k++;
            //}

            //int r = 1;

            //foreach (var item in data)
            //{
            //    row = sheet1.CreateRow(r);
            //    row.CreateCell(0).SetCellValue(item.OrderNO);
            //    row.CreateCell(1).SetCellValue(item.Imei);
            //    row.CreateCell(2).SetCellValue(item.SnNo);
            //    row.CreateCell(3).SetCellValue(item.CreateDateTime.ToString("yyyy-MM-dd HH:mm:ss"));
            //    row.CreateCell(4).SetCellValue(item.ContactName);
            //    row.CreateCell(5).SetCellValue(item.ContactPhone);
            //    row.CreateCell(6).SetCellValue(item.ProvinceName + " " + item.cityName);
            //    row.CreateCell(7).SetCellValue(item.Description);
            //    row.CreateCell(8).SetCellValue(item.ParameterName);
            //    row.CreateCell(9).SetCellValue(item.ProblemConfirmInfo);
            //    row.CreateCell(10).SetCellValue(item.RepairTypeName);
            //    row.CreateCell(11).SetCellValue(item.RepairStatusName);
            //    row.CreateCell(12).SetCellValue(item.NewRepairDateTime.ToString("yyyy-MM-dd HH:mm:ss"));
            //    r++;
            //}

            ////判断是否有此文件夹
            //if (!Directory.Exists(dir))
            //{
            //    Directory.CreateDirectory(dir);
            //}

            ////生成文件
            //FileStream file = new FileStream(dir + fileName, FileMode.Create);

            ////写入文件
            //hssfworkbook.Write(file);

            //file.Position = 0;

            //byte[] by = new byte[file.Length];

            //file.Read(by, 0, by.Length);
            //file.Close();

        //    return by;
        //}
        #endregion

        #region 导出数据通用模版
        /// <summary>
        /// 导出数据通用模版
        /// </summary>
        /// <param name="datas">实体数据集集合</param>
        /// <param name="list">表头列名,数据字段集合</param>
        /// <param name="dir">文件路径</param>
        /// <param name="fileName">文件名</param>
        /// <param name="sheetName">Excel页名</param>
        /// <returns>返回下载字节流</returns>
        public static byte[] Export<T>(IList<T> datas, List<ExcelEntity> list, string dir, string fileName, string sheetName)
        {
            //新建页面
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();
            ISheet sheet1 = hssfworkbook.CreateSheet(sheetName);

            //添加第一行数据
            IRow row = sheet1.CreateRow(0);

            #region 设置表头样式

            ICellStyle headerStyle = hssfworkbook.CreateCellStyle();
            IFont font = hssfworkbook.CreateFont();
            font.FontHeightInPoints = 13;
            font.Boldweight = short.MaxValue;
            headerStyle.SetFont(font);
            headerStyle.Alignment = HorizontalAlignment.CENTER;
            headerStyle.VerticalAlignment = VerticalAlignment.CENTER;
            headerStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THICK;
            headerStyle.BottomBorderColor = HSSFColor.RED.index;

            row.Height = 30 * 20;

            #endregion

            int k = 0;
            foreach (var item in list)
            {
                sheet1.SetColumnWidth(k, item.Width * 256);
                row.CreateCell(k).SetCellValue(item.ColumnName);
                row.GetCell(k).CellStyle = headerStyle;
                k++;
            }

            //添加数据
            int r = 1;
            foreach (var item in datas)
            {
                //创建行
                row = sheet1.CreateRow(r);

                //获取实体类型
                Type type = item.GetType();

                int s = 0;
                foreach (var column in list)
                {
                    //获取表字段集合
                    var tableColumnList = column.TableColumnName.Split(',');

                    //值
                    var value = "";

                    //如果表字段大于1
                    if (tableColumnList.Length > 1)
                    {
                        for (int d = 0; d < tableColumnList.Length; d++)
                        {
                            //获取实体行信息
                            var properInfos = type.GetProperty(tableColumnList[d]);

                            value = value + ((properInfos.GetValue(item, null) == null) ? "" : properInfos.GetValue(item, null).ToString()) + " ";
                        }
                    }
                    else
                    {
                        //获取实体行信息
                        var properInfos = type.GetProperty(column.TableColumnName);

                        //如果字段类型是时间，执行转换
                        if (properInfos.PropertyType.FullName == "System.DateTime")
                        {
                            var time = (properInfos.GetValue(item, null) == null) ? new DateTime(1900, 1, 1) : Convert.ToDateTime(properInfos.GetValue(item, null));

                            if (time.ToString("yyyy") == "1900")
                            {
                                value = "";
                            }
                            else
                            {
                                value = time.ToString("yyyy-MM-dd HH:mm:ss");
                            }
                        }
                        else if (properInfos.PropertyType.FullName == "System.Boolean")
                        {
                            var isTrue = (properInfos.GetValue(item, null) == null) ? false : Convert.ToBoolean(properInfos.GetValue(item, null));

                            value = isTrue ? "是" : "否";
                        }
                        else
                        {
                            value = (properInfos.GetValue(item, null) == null) ? "" : properInfos.GetValue(item, null).ToString();
                        }
                    }

                    //添加数据
                    row.CreateCell(s).SetCellValue(value);
                    s++;
                }

                r++;
            }

            //判断是否有此文件夹
            if (!Directory.Exists(dir))
            {
                Directory.CreateDirectory(dir);
            }

            //生成文件
            FileStream file = new FileStream(dir + fileName, FileMode.Create);

            //写入文件
            hssfworkbook.Write(file);

            file.Position = 0;

            byte[] by = new byte[file.Length];

            file.Read(by, 0, by.Length);
            file.Close();

            return by;
        }
        #endregion
    }
}
